import sqlite3, os
import logging, time
from tools.utils import code2openid
logger = logging.getLogger('info')

# 表1 用户id 设备id 是否预约 
# 表2 用户id 设备id 预约时间
# 表3 用户id 设备id 操作历史
# 表4 设备id 设备精度 设备维度 设备容量 设备小区 设备其他信息

## remained 保留字段
CREATE_TABLE1 = 'CREATE TABLE IF NOT EXISTS Table1 (userId text, deviceId text, subed text, remained text)'
CREATE_TABLE2 = 'CREATE TABLE IF NOT EXISTS Table2 (userId text, deviceId text, date text, remained text)'
CREATE_TABLE3 = 'CREATE TABLE IF NOT EXISTS Table3 (userId text, deviceId text, type text, date text， remained text)'
CREATE_TABLE4 = 'CREATE TABLE IF NOT EXISTS Table4 (deviceId text, lat real, lng real, Vol real, Commu text, remained1 text, remained2 text, remained3 text, remained4 text, remained5 text)'

class ownSqlDb:
    def __init__(self):
        # 创建数据库
        self.conn = sqlite3.connect( os.path.abspath(os.path.dirname(__file__)) + '/newDb.db', check_same_thread = False)
        c = self.conn.cursor()
        # c.execute('drop table if exists Table1')
        # c.execute('drop table if exists Table2')
        # c.execute('drop table if exists Table3')
        # 创建数据库
        for string in [CREATE_TABLE1, CREATE_TABLE2, CREATE_TABLE3, CREATE_TABLE4]:
            c.execute(string)
            self.conn.commit()
            logger.info('创建表成功 ： {}'.format(string))
        c.close()

    # 换取用户uniqId
    def uniq(self, userid):
        return code2openid(userid)

    # 用户注册
    def register(self, userid, deviceid):
        try:
            userid = self.uniq(userid)
            # 获取数据库游标
            cursor = self.conn.cursor()

            # 查询用户有无注册，若已经注册设备，返回错误信息
            findStr1 = 'SELECT * from Table1 where userId="{}"'.format(userid)
            result1 = cursor.execute(findStr1).fetchall()
            if result1:
                return {'errcode': -1, 'errmsg':'用户已绑定设备'}
            # 查询设备有无被绑定，若绑定返回错误
            findStr2 = 'SELECT * from Table1 where deviceId="{}"'.format(deviceid)
            result2 = cursor.execute(findStr2).fetchall()
            if result2:
                return {'errcode': -1, 'errmsg':'设备已被其他用户绑定'}

            insertStr = 'insert into Table1(userId, deviceId, subed, remained) values ("{}", "{}", "{}", "{}")'.format(userid, deviceid, 'succeed', 'keep')
            cursor.execute(insertStr) 
            print(insertStr)
            logger.info(' 用户:{} 注册了设备:{}'.format(userid, deviceid))
            self.conn.commit()
            cursor.close()
            return {'errcode': 0, 'errmsg': '注册成功'}
        except Exception as e:
            logger.warning(' 用户:{} 注册设备:{} 失败'.format(userid, deviceid))
            return {'errcode': -1, 'errmsg':'注册失败, {}'.format(e)}
    
    # 查询是否预约
    def checkSubOrNot(self, userid, deviceid):
        userid = self.uniq(userid)
        cursor = self.conn.cursor()
        findStr = 'SELECT * from Table1 where userId="{}" and deviceId="{}"'.format(userid, deviceid)
        result = cursor.execute(findStr).fetchall()
        cursor.close()
        if result:    
            return result[0][2] == 'succeed', result
        else:
            return False, result
    
    # 获取操作历史
    def getHistory(self, userid):
        userid = self.uniq(userid)
        cursor = self.conn.cursor()
        findStr = 'SELECT * from Table2 where userId="{}" '.format(userid)
        result = cursor.execute(findStr).fetchall()
        cursor.close()
        return {'errorcode': 0, 'result':result}

    # 预约行为
    def SetSub(self, userid, deviceid):
        userid = self.uniq(userid)
        # 获取数据库游标
        cursor = self.conn.cursor()
        flag, value = self.checkSubOrNot(userid, deviceid)
        if not value:
            # 操作记录放入表2
            insertStr2 = 'insert into Table2(userId, deviceId, date, remained) values ("{}", "{}", "{}", "{}")'.format(userid, deviceid, time.strftime('%y-%m-%d %H:%M:%S', time.localtime()), 'keep')
            cursor.execute(insertStr2)  
            info = '未在数据库中发现{}-{}的信息'.format(userid, deviceid) 
        else:
            updateStr = 'update Table1 set subed="{}" where userId="{}" and deviceId="{}"'.format('succeed', userid, deviceid)
            cursor.execute(updateStr) 
             # 操作记录放入表2
            insertStr2 = 'insert into Table2(userId, deviceId, date, remained) values ("{}", "{}", "{}", "{}")'.format(userid, deviceid, time.strftime('%y-%m-%d %H:%M:%S', time.localtime()), 'keep')
            cursor.execute(insertStr2)  
            info = '在数据库中有{}-{}的信息，修改对应值'.format(userid, deviceid)    
        # commit修改，释放油标
        self.conn.commit()
        cursor.close()
        return {'errcode':0, 'errmsg':info, 'userid':userid}
    
    # 动作完成取消预约
    def UnsetSub(self, userid, deviceid):
        userid = self.uniq(userid)
        # 获取数据库游标
        cursor = self.conn.cursor()
        flag, value = self.checkSubOrNot(userid, deviceid)
        if value and flag:
            updateStr = 'update Table1 set subed="{}" where userId="{}" and deviceId="{}"'.format('failed', userid, deviceid)
            cursor.execute(updateStr)    
        # commit修改，释放油标
        self.conn.commit()
        cursor.close()

    # 获取用户名下设备
    def getUserDevice(self, userid):
        try:
            # 设备信息
            deviceinfo = dict()
            userid = self.uniq(userid)
            cursor = self.conn.cursor()
            findStr = 'SELECT * from Table1 where userId="{}"'.format(userid)
            result = cursor.execute(findStr).fetchall()
            if result:
                deviceinfo['deviceid'] = result[0][1]
            
            # 获取设备其他信息
            findStr2 = 'SELECT * from Table4 where deviceId="{}"'.format(result[0][1])
            result2 = cursor.execute(findStr2).fetchall()
            if result2:
                deviceinfo['lat'] = result2[0][1]
                deviceinfo['lng'] = result2[0][2]
                deviceinfo['vol'] = result2[0][3]
                deviceinfo['commuName'] = result2[0][4]
            

            cursor.close()
            
            return {'errcode':0 , 'deviceInfo':deviceinfo}
        except Exception as e:
            return {'errcode':-1 , 'errmsg':e}

    # 获取预约状态
    def getSubStatus(self, deviceid):
        userid = self.uniq(userid)
        cursor = self.conn.cursor()
        findStr = 'SELECT * from Table1 where deviceId="{}"'.format(deviceid)
        result = cursor.execute(findStr).fetchall()
        cursor.close()
        if result:    
            if result[0][2] == 'succeed':
                return {'status': 'yes'}
            
        return {'status': 'no'} 
            





if __name__ == '__main__':
    odb = ownSqlDb()
    #odb.runSub('testuser', 'testdeviceid')
    print('1'*10, odb.checkSubOrNot('testuser', 'testdeviceid'))
    print('2'*10,odb.SetSub('testuser', 'testdeviceid'))
    print('3'*10,odb.checkSubOrNot('testuser', 'testdeviceid'))
    print('4'*10,odb.UnsetSub('testuser', 'testdeviceid'))
    print('5'*10,odb.checkSubOrNot('testuser', 'testdeviceid'))
    print('6'*10,odb.SetSub('testuser', 'testdeviceid'))
    print('7'*10,odb.checkSubOrNot('testuser', 'testdeviceid'))




